package com.thinkin.jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.thinkin.common.exception.JdbcException;
import com.thinkin.jdbc.driver.JdbcDriverUtils;

/**
 * 
 * @author cyt
 * 
 */
public class BaseDaoUtil {
	private Logger log = LoggerFactory.getLogger(this.getClass().getName());

	private Connection conn;
	private Statement st;
	private PreparedStatement pst;
	private ResultSet rs;
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

	/*
	 * public BaseDao(Connection conn){ this.conn = conn; try { this.st =
	 * conn.createStatement(); } catch (SQLException e) {
	 * log.error(e.getMessage(), e); } }
	 */

	public BaseDaoUtil() {
		this.conn = this.getConn();
		try {
			this.st = conn.createStatement();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);

		}
	}

	/**
	 * 执行不带参的sql语句
	 * 
	 * @param sql
	 */
	public void exeSql(String sql) throws JdbcException {
		try {
			st.executeUpdate(sql);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 执行带参的sql语句
	 */
	public int exeSql(String sql, Object param[]) throws JdbcException {
		int result = 0;
		try {
			pst = conn.prepareStatement(sql);
			if (param != null && param.length != 0) {
				for (int i = 0; i < param.length; i++) {
					pst.setObject(i + 1, param[i]);
				}
			}
			result = pst.executeUpdate();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return result;
	}

	public void exeSql(String sql, Object param) throws JdbcException {
		try {
			pst = conn.prepareStatement(sql);
			pst.setObject(0, param);
			pst.executeUpdate();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 执行查询sql语句
	 */
	public ResultSet getRs(String sql) throws Exception {
		try {
			rs = st.executeQuery(sql);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw e;
		}
		return rs;
	}

	/**
	 * 执行带参的sql语句
	 * 
	 * @param sql
	 * @param param
	 * @return
	 */
	public ResultSet getRs(String sql, Object param[]) throws JdbcException {
		try {
			pst = conn.prepareStatement(sql);
			if (param != null && param.length != 0) {
				for (int i = 0; i < param.length; i++) {
					pst.setObject(i + 1, param[i]);
				}
			}

			rs = pst.executeQuery();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return rs;
	}

	public ResultSet getRs(String sql, Object pk) throws JdbcException {
		try {
			pst = conn.prepareStatement(sql);
			pst.setObject(1, pk);
			rs = pst.executeQuery();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return rs;
	}

	/**
	 * 开启事务
	 */
	public void beginTransaction(Connection conn) throws JdbcException {
		try {
			if (conn != null) {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 开启事务
	 */
	public void beginTransaction() throws JdbcException {
		try {
			if (conn != null) {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 提交事务
	 */
	public void commitTransaction(Connection conn) throws JdbcException {
		try {
			if (conn != null) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 提交一个事务
	 */
	public void commitTransaction() throws JdbcException {
		try {
			if (conn != null) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 回滚一个事务
	 */

	public void rollbackTransaction() throws JdbcException {
		try {
			if (conn != null) {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}

	/**
	 * 事务回滚
	 */
	public static void rollbackTransaction(Connection conn)
			throws JdbcException {
		try {
			if (conn != null) {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();

		}
	}

	/**
	 * 取得自动增长的主键号
	 */
	public int getId() {
		int i = 0;
		String sql = "select @@identity";

		try {
			rs = st.executeQuery(sql);
			if (rs.next()) {
				i = rs.getInt(1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return i;
	}

	/**
	 * 重置事务开启状态
	 */
	public static void resetConnection(Connection conn) {
		try {
			if (conn != null) {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false);
				} else {
					conn.setAutoCommit(true);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();

		}
	}

	public void close(ResultSet rs, Statement st, Connection conn)
			throws JdbcException {
		try {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					log.error(e.getMessage(), e);
				}
		} finally {
			try {
				if (st != null)
					try {
						st.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						log.error(e.getMessage(), e);

					}
			} finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						log.error(e.getMessage(), e);

					}
			}

		}

	}

	/**
	 * 关闭数据库的资源
	 * 
	 */
	public void closeAll() {
		try {
			if (rs != null)
				rs.close();
			if (pst != null)
				pst.close();
			if (st != null)
				st.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			log.error(e.getMessage(), e);

		}
	}

	/**
	 * 使用线程安全的形式来获取数据库连接
	 */
	public Connection getConn() {
		Connection connThread = null;
		connThread = threadLocal.get();

		try {
			if (connThread == null || connThread.isClosed()) {
				connThread = JdbcDriverUtils.getJdbcInstance().getConn();
				threadLocal.set(connThread);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
		return connThread;
	}
}
